<?php
$sql[] = "CREATE TABLE IF NOT EXISTS `t_categories` (`id` INTEGER PRIMARY KEY  DEFAULT '', `name` VARCHAR DEFAULT '', `color` VARCHAR DEFAULT '', `maxAmountPerMonth` INTEGER DEFAULT '0');";

$sql[] = "CREATE TABLE IF NOT EXISTS `t_items` (`id` INTEGER PRIMARY KEY  DEFAULT '', `amount` FLOAT DEFAULT '', `operationDate` DATETIME DEFAULT '', `payee` VARCHAR DEFAULT '', `memo` VARCHAR DEFAULT '', `cleared` VARCHAR DEFAULT '', `category` INTEGER DEFAULT '', `createDate` DATETIME DEFAULT '', `debit` INTEGER DEFAULT '', `checkSum` VARCHAR DEFAULT '', `comments` VARCHAR DEFAULT '', `deleteFlag` INTEGER DEFAULT 0, `investmentFlag` INTEGER DEFAULT 0, `importId` INTEGER DEFAULT ``);";

$sql[] = "CREATE TABLE IF NOT EXISTS `t_keywords` (`id` INTEGER PRIMARY KEY  DEFAULT '', `keyword` VARCHAR DEFAULT '', `category` INTEGER DEFAULT '');";

$sql[] = "CREATE TABLE IF NOT EXISTS `t_settings` (`id` INTEGER PRIMARY KEY  DEFAULT '' ,`language` VARCHAR DEFAULT '' ,`username` VARCHAR DEFAULT '' ,`password` VARCHAR DEFAULT '' ,`secureAccess` INTEGER DEFAULT '' ,`setupDone` INTEGER DEFAULT '' ,`currency` VARCHAR DEFAULT '' , `initialBalance` INTEGER DEFAULT 0, `cb_version` VARCHAR DEFAULT '0.9.3');";

// new as of 0.7.1
$sql[] = "CREATE TABLE IF NOT EXISTS `t_version` (`id` INTEGER PRIMARY KEY  DEFAULT '' ,`cb_version` VARCHAR DEFAULT '0.9.3');";

// new as of 0.7.2
$sql[] = "CREATE TABLE IF NOT EXISTS `t_imports` (`id` INTEGER PRIMARY KEY  DEFAULT '' ,`originalFileName` VARCHAR DEFAULT '' ,`importCount` INTEGER DEFAULT '' ,`importDuplicate` INTEGER DEFAULT '' ,`importDate` DATETIME DEFAULT '', `importType` INTEGER DEFAULT '');";

// deprecated as of 0.7.1
// $sql[6] = "CREATE VIEW IF NOT EXISTS 'v_all_month' AS SELECT distinct strftime(\"%m-%Y\", operationDate) as month FROM t_items ORDER BY month DESC;";

// new as of 0.7.1
$sql[] = "CREATE VIEW IF NOT EXISTS 'v_distinct_month' AS SELECT distinct strftime(\"%Y-%m-01\", operationDate) as month FROM t_items WHERE deleteFlag=0 ORDER BY operationDate DESC;";

$sql[] = "CREATE VIEW IF NOT EXISTS 'v_categories' AS select * from t_categories ORDER BY name ASC;";

//update the version to 0.8.1
$sql[] = "CREATE VIEW IF NOT EXISTS 'v_items' AS  SELECT t_items.id as id, amount, debit, payee, memo, operationDate, category, t_categories.name as categoryName, comments, deleteFlag, investmentFlag FROM t_items JOIN t_categories on t_items.category = t_categories.id WHERE deleteFlag = 0 ORDER BY date(operationDate) DESC;";

$sql[] = "CREATE VIEW IF NOT EXISTS 'v_items_unfiled' AS SELECT t_items.id as id, amount, debit, payee, memo, operationDate, category, comments, t_categories.name as categoryName, comments FROM t_items JOIN t_categories on t_items.category = t_categories.id WHERE category = \"1\" AND deleteFlag = 0 ORDER BY date(operationDate) DESC;";

$sql[] = "CREATE VIEW IF NOT EXISTS 'v_settings' AS SELECT * FROM t_settings LIMIT 0,1;";
//updated on 0.8.1
$sql[] = "CREATE VIEW IF NOT EXISTS 'v_total_amount' AS  SELECT count(amount) as count, total(amount) as total, strftime(\"%m\", operationDate) as month, strftime(\"%Y\", operationDate) as year, debit, category, name, color, maxAmountPerMonth, strftime(\"%m-%Y\", operationDate) as limitMonth, operationDate FROM t_items JOIN t_categories ON t_categories.id=t_items.category WHERE deleteFlag=0 GROUP BY debit, category, strftime(\"%Y-%m\", operationDate) ORDER BY operationDate ASC;";

// new as of 0.8.1
$sql[] = "CREATE VIEW IF NOT EXISTS `v_suggestions_items` AS SELECT id, payee, memo,operationDate  FROM t_items WHERE deleteFlag=0 ORDER BY operationDate DESC;";

$sql[] = "CREATE UNIQUE INDEX IF NOT EXISTS `i_items` ON `t_items` (`checkSum` ASC);";

$sql[] = "CREATE UNIQUE INDEX IF NOT EXISTS `i_keyword` ON `t_keywords` (`keyword` ASC);";

// new as of 0.7.1
$sql[] = "CREATE UNIQUE INDEX IF NOT EXISTS `i_category` ON `t_categories` (`name` ASC);";

// new as of 0.9.1
$sql[] = "CREATE TABLE t_report_settings (`id` INTEGER PRIMARY KEY  NOT NULL ,`name` VARCHAR,`minDate` VARCHAR,`maxDate` VARCHAR,`activeFlag` INTEGER, `categories` VARCHAR, `type` VARCHAR, `range` INTEGER, `debit` INTEGER, `credit` INTEGER)";
$sql[] = "INSERT INTO t_report_settings (`name`, `type`, `range`, `activeFlag`, `categories`, `debit`, `credit`) VALUES ('3', 'range', '3', '0', '', '1', '1');";
$sql[] = "INSERT INTO t_report_settings (`name`, `type`, `range`, `activeFlag`, `categories`, `debit`, `credit`) VALUES ('6', 'range', '6', '0', '', '1', '1');";
$sql[] = "INSERT INTO t_report_settings (`name`, `type`, `range`, `activeFlag`, `categories`, `debit`, `credit`) VALUES ('12', 'range', '12', '1', '', '1', '1');";

// new as of 0.9.1
$sql[] = "CREATE VIEW IF NOT EXISTS 'v_total_balance' AS SELECT total(amount) as total, debit FROM t_items WHERE deleteFlag=0 GROUP BY debit";

// new as of 0.9.1
$sql[] = "CREATE UNIQUE INDEX i_unique_report_name ON t_report_settings (`name` ASC)";

$sql[] = "INSERT INTO t_version (`cb_version`) VALUES ('0.9.3');";

$sql[] = "INSERT INTO t_categories (`name`,`color`) VALUES ('uncategorized', '990000');";

?>